# @FileName  :  populate_data.py
# @Time      :  2025/8/17
# @Author    :  Assistant
# @info      :  数据库数据填充脚本

import asyncio
import random
from datetime import datetime, date, timedelta
from tortoise import Tortoise, run_async
from tortoise.functions import Sum
from faker import Faker
from app.models.models import *

# 初始化Faker，使用中文
fake = Faker('zh_CN')


class DataPopulator:
    def __init__(self):
        self.areas = []
        self.dictionaries = {}
        self.teachers = []
        self.agent = None
        self.students = []
        self.courses = []

    async def init_db(self):
        """初始化数据库连接"""
        TORTOISE_ORM = {
            "connections": {
                "default": {
                    "engine": "tortoise.backends.mysql",
                    "credentials": {
                        "host": "127.0.0.1",
                        "port": "3306",
                        "user": "root",
                        "password": "root",
                        "database": "g_project",
                    }
                }
            },
            "apps": {
                "models": {
                    "models": ["app.models.models"],
                    "default_connection": "default",
                }
            },
            "timezone": "Asia/Shanghai",
        }

        await Tortoise.init(config=TORTOISE_ORM)
        # await Tortoise.generate_schemas()

    async def close_db(self):
        """关闭数据库连接"""
        await Tortoise.close_connections()

    async def clear_all_data(self):
        """清空所有表的数据"""
        print("正在清空数据库数据...")

        # 按照外键依赖关系的逆序删除数据
        tables_to_clear = [
            EnrollmentRecord,  # 报名记录
            CourseRecord,  # 课程记录
            Honor,  # 荣誉
            Achievement,  # 学员风采
            Resource,  # 资源
            News,  # 资讯
            SignUp,  # 报名
            Course,  # 课程
            Student,  # 学员
            Teacher,  # 讲师
            Agent,  # 培训机构
            Dictionary,  # 字典
            Area,  # 地区
        ]

        for table in tables_to_clear:
            await table.all().delete()
            print(f"已清空 {table.__name__} 表")

        print("数据清理完成！")

    async def populate_all(self):
        """执行所有数据填充操作"""
        try:
            await self.init_db()
            print("开始填充数据库数据...")

            # 先清空所有数据
            await self.clear_all_data()

            # 然后重新填充
            await self.populate_areas()
            await self.populate_dictionaries()
            await self.populate_teachers()
            await self.populate_agent()
            await self.populate_students()
            await self.populate_courses()
            await self.populate_signups_and_enrollments()
            await self.populate_course_records()
            await self.populate_honors()
            await self.populate_achievements()
            await self.populate_news()
            await self.populate_resources()
            await self.update_statistics()

            print("数据填充完成！")
            print(f"- 地区数量: {len(self.areas)}")
            print(f"- 字典数据: {sum(len(v) for v in self.dictionaries.values())}")
            print(f"- 讲师数量: {len(self.teachers)}")
            print(f"- 培训机构: 1")
            print(f"- 学员数量: {len(self.students)}")
            print(f"- 课程数量: {len(self.courses)}")
            print(f"- 报名记录: {await EnrollmentRecord.all().count()}")
            print(f"- 课程记录: {await CourseRecord.all().count()}")
            print(f"- 荣誉记录: {await Honor.all().count()}")
            print(f"- 风采记录: {await Achievement.all().count()}")
            print(f"- 资讯数量: {await News.all().count()}")
            print(f"- 资源数量: {await Resource.all().count()}")

        except Exception as e:
            print(f"数据填充过程中出现错误: {e}")
            raise
        finally:
            await self.close_db()

    async def populate_areas(self):
        """填充地区数据"""
        print("正在填充地区数据...")
        area_data = [
            {"name": "北京市", "code": "110000"},
            {"name": "上海市", "code": "310000"},
            {"name": "天津市", "code": "120000"},
            {"name": "重庆市", "code": "500000"},
            {"name": "河北省", "code": "130000"},
            {"name": "山西省", "code": "140000"},
            {"name": "内蒙古自治区", "code": "150000"},
            {"name": "辽宁省", "code": "210000"},
            {"name": "吉林省", "code": "220000"},
            {"name": "黑龙江省", "code": "230000"},
            {"name": "江苏省", "code": "320000"},
            {"name": "浙江省", "code": "330000"},
            {"name": "安徽省", "code": "340000"},
            {"name": "福建省", "code": "350000"},
            {"name": "江西省", "code": "360000"},
            {"name": "山东省", "code": "370000"},
            {"name": "河南省", "code": "410000"},
            {"name": "湖北省", "code": "420000"},
            {"name": "湖南省", "code": "430000"},
            {"name": "广东省", "code": "440000"},
        ]

        for area_info in area_data:
            area = await Area.create(**area_info)
            self.areas.append(area)

    async def populate_dictionaries(self):
        """填充字典数据"""
        print("正在填充字典数据...")
        dict_data = {
            DictionaryType.TEACHER_CATEGORY: [
                {"code": "senior", "name": "高级讲师", "value": "senior"},
                {"code": "intermediate", "name": "中级讲师", "value": "intermediate"},
                {"code": "junior", "name": "初级讲师", "value": "junior"},
                {"code": "guest", "name": "客座讲师", "value": "guest"},
            ],
            DictionaryType.STUDENT_SOURCE: [
                {"code": "online", "name": "线上推广", "value": "online"},
                {"code": "referral", "name": "朋友推荐", "value": "referral"},
                {"code": "agent", "name": "机构招生", "value": "agent"},
                {"code": "government", "name": "政府推荐", "value": "government"},
                {"code": "self", "name": "自主报名", "value": "self"},
            ],
            DictionaryType.COURSE_CATEGORY: [
                {"code": "planting", "name": "种植技术", "value": "planting"},
                {"code": "breeding", "name": "养殖技术", "value": "breeding"},
                {"code": "processing", "name": "农产品加工", "value": "processing"},
                {"code": "marketing", "name": "农业营销", "value": "marketing"},
                {"code": "management", "name": "农场管理", "value": "management"},
                {"code": "ecommerce", "name": "农村电商", "value": "ecommerce"},
            ],
            DictionaryType.NEWS_CATEGORY: [
                {"code": "policy", "name": "政策解读", "value": "policy"},
                {"code": "technology", "name": "技术资讯", "value": "technology"},
                {"code": "market", "name": "市场动态", "value": "market"},
                {"code": "success", "name": "成功案例", "value": "success"},
                {"code": "training", "name": "培训动态", "value": "training"},
            ],
            DictionaryType.RESOURCE_CATEGORY: [
                {"code": "video", "name": "视频资料", "value": "video"},
                {"code": "document", "name": "文档资料", "value": "document"},
                {"code": "image", "name": "图片资料", "value": "image"},
                {"code": "software", "name": "软件工具", "value": "software"},
                {"code": "template", "name": "模板资料", "value": "template"},
            ],
        }

        for dict_type, items in dict_data.items():
            self.dictionaries[dict_type] = []
            for i, item in enumerate(items):
                dictionary = await Dictionary.create(
                    type=dict_type,
                    code=item['code'],
                    name=item['name'],
                    value=item['value'],
                    sort_order=i + 1,
                    description=f"{dict_type.value}类型的{item['name']}"
                )
                self.dictionaries[dict_type].append(dictionary)

    async def populate_teachers(self):
        """填充讲师数据"""
        print("正在填充讲师数据...")
        specialties = [
            "水稻种植技术", "蔬菜种植技术", "果树栽培", "花卉种植",
            "生猪养殖", "家禽养殖", "水产养殖", "特种养殖",
            "农产品加工", "食品安全", "农业机械", "农业电商",
            "农场管理", "农业财务", "病虫害防治", "土壤改良"
        ]

        universities = [
            "中国农业大学", "南京农业大学", "华中农业大学", "西北农林科技大学",
            "北京林业大学", "东北农业大学", "四川农业大学", "华南农业大学",
            "湖南农业大学", "山东农业大学", "福建农林大学", "上海海洋大学"
        ]

        levels = ["教授", "副教授", "讲师", "助教", "高级农艺师", "农艺师"]

        for i in range(25):
            teacher = await Teacher.create(
                teacher_id=f"T{str(i + 1).zfill(4)}",
                name=fake.name(),
                phone=fake.phone_number(),
                id_card=fake.ssn(),
                specialty=random.choice(specialties),
                hire_date=fake.date_between(start_date='-10y', end_date='today'),
                education=f"{random.choice(universities)}，{random.choice(['本科', '硕士', '博士'])}学历，{random.choice(['农学', '植物保护', '动物科学', '农业经济管理'])}专业",
                sessions_count=random.randint(10, 200),
                level=random.choice(levels),
                category=random.choice(self.dictionaries[DictionaryType.TEACHER_CATEGORY]),
                notes=f"专业从事{random.choice(specialties)}相关教学工作"
            )
            self.teachers.append(teacher)

    async def populate_agent(self):
        """填充培训机构数据（只创建一个）"""
        print("正在填充培训机构数据...")
        self.agent = await Agent.create(
            name="农业技能培训中心",
            credit_code="91110000MA001234XY",
            license_expire=date.today() + timedelta(days=365 * 3),
            business_scope="农业技术培训、职业技能培训、农业咨询服务、农产品销售",
            legal_person="张三丰",
            phone="400-123-4567",
            trainee_count=0,  # 后续会更新
            training_sessions=0,  # 后续会更新
            service_direction="现代农业技术培训、农村电商培训、农业创业指导",
            is_approved=True,
            area=random.choice(self.areas)
        )

    async def populate_students(self):
        """填充学员数据"""
        print("正在填充学员数据...")
        work_experiences = [
            "从事水稻种植5年，有丰富的田间管理经验",
            "家庭农场经营者，主要种植蔬菜",
            "养殖合作社成员，专业养猪",
            "农产品电商从业者",
            "农村创业青年，希望学习新技术",
            "退役军人，准备从事农业相关工作",
            "大学毕业生，有志于现代农业发展",
            "传统农民，希望转型升级"
        ]

        for i in range(150):
            student = await Student.create(
                student_id=f"S{str(i + 1).zfill(6)}",
                name=fake.name(),
                gender=random.choice([Gender.MALE, Gender.FEMALE]),
                phone=fake.phone_number(),
                birthday=fake.date_of_birth(minimum_age=18, maximum_age=65),
                id_card=fake.ssn(),
                is_verified=random.choice([True, False]),
                wechat=f"wx_{fake.user_name()}",
                emergency_contact=fake.name(),
                emergency_phone=fake.phone_number(),
                source=random.choice(self.dictionaries[DictionaryType.STUDENT_SOURCE]),
                work_experience=random.choice(work_experiences),
                total_study_hours=random.randint(0, 500),
                honors_count=random.randint(0, 10),
                notes=f"来自{fake.city()}，{random.choice(['积极主动', '学习能力强', '实践经验丰富', '有创新精神'])}",
                area=random.choice(self.areas)
            )
            self.students.append(student)

    async def populate_courses(self):
        """填充课程数据"""
        print("正在填充课程数据...")
        course_titles = [
            "现代水稻种植技术与管理", "蔬菜大棚种植技术", "果树嫁接与修剪技术",
            "生猪科学养殖技术", "肉鸡高效养殖方法", "水产养殖病害防治",
            "农产品质量安全控制", "农产品深加工技术", "农村电商运营实务",
            "农场财务管理", "农业机械使用与维护", "植物病虫害识别与防治",
            "土壤改良与施肥技术", "农业合作社管理", "农业项目申报与管理",
            "现代农业园区规划", "农业品牌建设与营销", "农业保险知识",
            "农村创业指导", "农业政策解读", "绿色农业发展", "智慧农业技术应用",
            "农产品电商直播", "农业供应链管理", "农村金融服务"
        ]

        tags_pool = [
            "实用技术", "科学种植", "高效养殖", "电商营销", "创业指导",
            "政策解读", "品牌建设", "质量安全", "绿色发展", "智慧农业"
        ]

        for i, title in enumerate(course_titles):
            # 随机选择课程类型
            is_online = random.choice([True, False])
            schedule = None
            if not is_online:  # 线下课程需要排课
                schedule = fake.date_time_between(start_date='+1d', end_date='+90d')

            course = await Course.create(
                title=title,
                parent_course=None,  # 暂不设置父课程
                category=random.choice(self.dictionaries[DictionaryType.COURSE_CATEGORY]),
                is_online=is_online,
                cover_image=f"https://example.com/course_covers/course_{i + 1}.jpg",
                video_resource=f"video_resource_{i + 1}" if is_online else None,
                live_url=f"https://live.example.com/room_{i + 1}" if not is_online else None,
                schedule=schedule,
                is_premium=random.choice([True, False]),
                enable_checkin=not is_online,  # 线下课程启用签到
                enrollment_count=random.randint(10, 80),
                notes=f"本课程适合{random.choice(['初学者', '有基础学员', '进阶学员'])}学习",
                tags=random.sample(tags_pool, random.randint(2, 4)),
                agent=self.agent,
                teacher=random.choice(self.teachers)
            )
            self.courses.append(course)

    async def populate_signups_and_enrollments(self):
        """填充报名和报名记录数据"""
        print("正在填充报名数据...")
        for course in self.courses:
            # 为每个课程创建1-2个报名批次
            signup_count = random.randint(1, 2)
            for batch in range(signup_count):
                signup = await SignUp.create(
                    start_date=fake.date_time_between(start_date='-30d', end_date='-10d'),
                    end_date=fake.date_time_between(start_date='-9d', end_date='+30d'),
                    max_capacity=random.randint(30, 100),
                    course=course
                )

                # 为每个报名批次随机分配学员
                enrolled_students = random.sample(
                    self.students,
                    min(random.randint(5, 25), len(self.students))
                )

                for student in enrolled_students:
                    await EnrollmentRecord.create(
                        signup=signup,
                        student=student,
                        signup_time=fake.date_time_between(
                            start_date=signup.start_date,
                            end_date=signup.end_date
                        )
                    )

    async def populate_course_records(self):
        """填充课程记录数据"""
        print("正在填充课程记录数据...")
        enrollments = await EnrollmentRecord.all().prefetch_related('signup', 'student')

        for enrollment in enrollments:
            # 为每个报名记录创建1-3个课程记录
            record_count = random.randint(1, 3)
            for _ in range(record_count):
                status = random.choice(list(CheckinStatus))
                checkin_time = None
                if status != CheckinStatus.NOT_CHECKED_IN:
                    checkin_time = fake.date_time_between(start_date='-60d', end_date='now')

                # 确保获取实际的对象而不是QuerySet
                signup = await enrollment.signup
                course = await signup.course

                await CourseRecord.create(
                    course=course,
                    student=enrollment.student,
                    checkin_time=checkin_time,
                    status=status,
                    study_duration=random.randint(30, 180)
                )

    async def populate_honors(self):
        """填充荣誉数据"""
        print("正在填充荣誉数据...")
        honor_names = [
            "优秀学员", "学习标兵", "实践能手", "创新之星", "进步之星",
            "技能大师", "最佳实践奖", "学习成果奖", "优秀毕业生", "杰出贡献奖"
        ]

        # 随机为一些学员颁发荣誉
        honored_students = random.sample(self.students, random.randint(20, 50))

        for student in honored_students:
            # 每个学员获得1-3个荣誉
            honor_count = random.randint(1, 3)
            student_courses = await CourseRecord.filter(student=student).prefetch_related('course')

            if student_courses:
                for _ in range(honor_count):
                    course_record = random.choice(student_courses)
                    await Honor.create(
                        name=random.choice(honor_names),
                        type=random.choice(list(HonorType)),
                        image_url=f"https://example.com/honors/{fake.uuid4()}.png",
                        issue_date=fake.date_between(start_date='-365d', end_date='today'),
                        course=course_record.course,
                        student=student
                    )

    async def populate_achievements(self):
        """填充学员风采数据"""
        print("正在填充学员风采数据...")
        achievement_titles = [
            "成功创建家庭农场", "带领村民致富", "获得省级农业技能竞赛奖项",
            "创新种植技术获得专利", "电商销售突破百万", "建立农业合作社",
            "推广绿色种植技术", "发展特色农业产业", "农产品品牌化经营成功",
            "技术推广惠及千家万户"
        ]

        descriptions = [
            "通过在平台的学习，掌握了先进的农业技术，成功创业致富。",
            "运用所学电商知识，帮助当地农产品打开销路。",
            "将培训中学到的科学种植方法应用到实践中，取得显著成效。",
            "结合理论学习和实践操作，在农业技能竞赛中获得优异成绩。",
            "通过培训提升了管理能力，成功组织建立了农业合作社。"
        ]

        # 随机为一些学员创建成就记录
        achievers = random.sample(self.students, random.randint(30, 60))

        for student in achievers:
            achievement_count = random.randint(1, 2)
            for _ in range(achievement_count):
                await Achievement.create(
                    title=random.choice(achievement_titles),
                    description=random.choice(descriptions),
                    image_url=f"https://example.com/achievements/{fake.uuid4()}.jpg",
                    student=student,
                    course=random.choice(self.courses) if random.choice([True, False]) else None
                )

    async def populate_news(self):
        """填充资讯数据"""
        print("正在填充资讯数据...")
        news_data = [
            {
                "title": "农业农村部发布2025年农业技能培训新政策",
                "abstract": "为进一步提升农民技能水平，促进乡村振兴，农业农村部发布了最新的农业技能培训政策...",
                "content": "详细的政策解读内容...",
                "category": "policy"
            },
            {
                "title": "智慧农业技术在现代农场的应用实践",
                "abstract": "物联网、大数据、人工智能等技术在农业生产中的应用越来越广泛...",
                "content": "详细的技术应用介绍...",
                "category": "technology"
            },
            {
                "title": "2025年农产品市场行情分析与预测",
                "abstract": "根据市场调研和数据分析，预测2025年主要农产品的市场走势...",
                "content": "详细的市场分析报告...",
                "category": "market"
            },
        ]

        # 扩展新闻数据
        for i in range(30):
            category_key = random.choice(list(self.dictionaries[DictionaryType.NEWS_CATEGORY]))
            category = random.choice(self.dictionaries[DictionaryType.NEWS_CATEGORY])

            await News.create(
                title=f"{fake.sentence(nb_words=8)} - 第{i + 1}期",
                cover_image=f"https://example.com/news_covers/news_{i + 1}.jpg",
                abstract=fake.text(max_nb_chars=200),
                content=fake.text(max_nb_chars=2000),
                is_top=random.choice([True, False]) if i < 5 else False,
                tags=random.sample(["农业政策", "技术创新", "市场动态", "成功案例", "培训资讯"],
                                   random.randint(1, 3)),
                category=category
            )

    async def populate_resources(self):
        """填充资源数据"""
        print("正在填充资源数据...")
        resource_names = [
            "现代农业种植技术手册", "农产品质量安全标准", "农业机械操作指南",
            "农村电商运营手册", "农业财务管理模板", "病虫害防治图谱",
            "农业政策汇编", "农业创业计划书模板", "农产品包装设计素材",
            "农业培训视频合集", "农业技术标准文档", "农场管理软件工具"
        ]

        file_formats = ["pdf", "doc", "xls", "mp4", "zip", "ppt"]

        for i, name in enumerate(resource_names):
            file_format = random.choice(file_formats)
            await Resource.create(
                name=name,
                code=f"RES{str(i + 1).zfill(4)}",
                cloud_id=f"cloud_resource_{fake.uuid4()}",
                file_format=file_format,
                file_size=f"{random.randint(1, 500)}MB",
                download_count=random.randint(0, 1000),
                expiry_date=fake.date_between(start_date='+30d', end_date='+365d'),
                category=random.choice(self.dictionaries[DictionaryType.RESOURCE_CATEGORY]),
                teacher=random.choice(self.teachers),
                course=random.choice(self.courses) if random.choice([True, False]) else None
            )


    async def populate_all(self):
        """执行所有数据填充操作"""
        try:
            await self.init_db()
            print("开始填充数据库数据...")

            await self.clear_all_data()
            await self.populate_areas()
            await self.populate_dictionaries()
            await self.populate_teachers()
            await self.populate_agent()
            await self.populate_students()
            await self.populate_courses()
            await self.populate_signups_and_enrollments()
            await self.populate_course_records()
            await self.populate_honors()
            await self.populate_achievements()
            await self.populate_news()
            await self.populate_resources()
            # await self.update_statistics()

            print("数据填充完成！")
            print(f"- 地区数量: {len(self.areas)}")
            print(f"- 字典数据: {sum(len(v) for v in self.dictionaries.values())}")
            print(f"- 讲师数量: {len(self.teachers)}")
            print(f"- 培训机构: 1")
            print(f"- 学员数量: {len(self.students)}")
            print(f"- 课程数量: {len(self.courses)}")
            print(f"- 报名记录: {await EnrollmentRecord.all().count()}")
            print(f"- 课程记录: {await CourseRecord.all().count()}")
            print(f"- 荣誉记录: {await Honor.all().count()}")
            print(f"- 风采记录: {await Achievement.all().count()}")
            print(f"- 资讯数量: {await News.all().count()}")
            print(f"- 资源数量: {await Resource.all().count()}")

        except Exception as e:
            print(f"数据填充过程中出现错误: {e}")
            raise
        finally:
            await self.close_db()


# 运行脚本
if __name__ == "__main__":
    populator = DataPopulator()
    run_async(populator.populate_all())